
package model.dao;
import model.entities.Bill;
import model.entities.Student;
import model.entities.User;
import model.entities.Course;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.dao.service.BillDAOService;



public class BillDAO implements BillDAOService {
    private static BillDAO billDAO;
    
    public static BillDAO getInstance(){
        if(billDAO == null){
        billDAO = new BillDAO();
        }
        return billDAO;
    
    }
    
    @Override
    public List<Bill> getBillList(){
       ArrayList<Bill> listBill = new ArrayList<Bill>(); 
       Connection con = ConnectionFactory.getConnection();
       String sql = "select * from tblBill";
        PreparedStatement pstmt;
        try {
            pstmt = con.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
            Bill bll = new Bill();
            bll.setBillID(rs.getInt("BillID"));
            bll.setDate(rs.getDate("Date"));
            bll.setMoneyAmount(rs.getDouble("MoneyAmount"));
            bll.setMonth(rs.getInt("Month"));
            bll.setYear(rs.getInt("Year"));
            bll.setNotes(rs.getString("Notes"));
            User user = UserDAO.getInstance().getUserByID(rs.getInt("UserID"));
            bll.setUser(user);
            Student std = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
                 bll.setStudent(std);           
            Course course = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
                 bll.setCourse(course);
            listBill.add(bll);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BillDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listBill;
    }
    
    @Override
    public Bill getBillByID(int billID){
        Bill bll = new Bill();
        Connection con = ConnectionFactory.getConnection();
        String sql = "select * from tblBill where BillID = ?";
        PreparedStatement pstmt;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, billID);
            ResultSet rs = pstmt.executeQuery();
            bll.setBillID(rs.getInt("BillID"));
            bll.setDate(rs.getDate("Date"));
            bll.setMoneyAmount(rs.getDouble("MoneyAmount"));
            bll.setMonth(rs.getInt("Month"));
            bll.setYear(rs.getInt("Year"));
            bll.setNotes(rs.getString("Notes"));
            User user = UserDAO.getInstance().getUserByID(rs.getInt("UserID"));
                bll.setUser(user);
            Student std = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
                 bll.setStudent(std);           
            Course course = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
            bll.setCourse(course);
        } catch (SQLException ex) {
            Logger.getLogger(BillDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return bll;
    } 
    
    @Override
    public boolean insertBillDAO(model.entities.Bill bill){
        String query = "INSERT INTO tblBill VALUES(?,?,?,?,?,?,?,?)";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setDate(1, (Date) bill.getDate());
            pstmt.setDouble(2, bill.getMoneyAmount());
            pstmt.setInt(3, bill.getMonth());
            pstmt.setInt(4, bill.getYear());
            pstmt.setString(5, bill.getNotes());
            pstmt.setInt(6, bill.getUser().getUserID());
            pstmt.setInt(7, bill.getStudent().getStudentID());
            pstmt.setInt(8, bill.getCourse().getCourseID());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(BillDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    @Override
    public boolean updateBillDAO(model.entities.Bill bill){
        String query = "UPDATE tblBill SET Date=?, MoneyAmount=?, [Month]=?, [Year]=?, Notes=?,"
                + " UserID=?, StudentID=?, CourseID=? WHERE BillID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setDate(1, (Date) bill.getDate());
            pstmt.setDouble(2, bill.getMoneyAmount());
            pstmt.setInt(3, bill.getMonth());
            pstmt.setInt(4, bill.getYear());
            pstmt.setString(5, bill.getNotes());
            pstmt.setInt(6, bill.getUser().getUserID());
            pstmt.setInt(7, bill.getStudent().getStudentID());
            pstmt.setInt(8, bill.getCourse().getCourseID());
            pstmt.setInt(9, bill.getBillID());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(BillDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    @Override
    public boolean deleteBillDAO(int billID){
        String query = "DELETE FROM tblBill WHERE BillID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, billID);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(BillDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        
    }

    @Override
    public List<Bill> findBillByID(int billId) {
        List<Bill> lstBill = new ArrayList<Bill>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblBill WHERE BillID=?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, billId);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Bill b = new Bill();
                b.setBillID(rs.getInt("BillID"));
                b.setDate(rs.getDate("Date"));
                b.setMoneyAmount(rs.getDouble("MoneyAmount"));
                b.setMonth(rs.getInt("Month"));
                b.setYear(rs.getInt("Year"));
                b.setNotes(rs.getNString("Notes"));
                User u = UserDAO.getInstance().getUserByID(rs.getInt("UserID"));
                b.setUser(u);
                Student stu = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
                b.setStudent(stu);
                Course c = CourseDAO.getInstance().getCourseByID(rs.getInt(rs.getInt("CourseID")));
                b.setCourse(c);
                lstBill.add(b);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BillDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstBill;
    }

    @Override
    public List<Bill> findBillByDate(java.util.Date date) {
        List<Bill> lstBill = new ArrayList<Bill>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblBill WHERE Date=?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setDate(1, (Date) date);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Bill b = new Bill();
                b.setBillID(rs.getInt("BillID"));
                b.setDate(rs.getDate("Date"));
                b.setMoneyAmount(rs.getDouble("MoneyAmount"));
                b.setMonth(rs.getInt("Month"));
                b.setYear(rs.getInt("Year"));
                b.setNotes(rs.getNString("Notes"));
                User u = UserDAO.getInstance().getUserByID(rs.getInt("UserID"));
                b.setUser(u);
                Student stu = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
                b.setStudent(stu);
                Course c = CourseDAO.getInstance().getCourseByID(rs.getInt(rs.getInt("CourseID")));
                b.setCourse(c);
                lstBill.add(b);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BillDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstBill;
    }

    @Override
    public List<Bill> findBillByUserID(int userID) {
        List<Bill> lstBill = new ArrayList<Bill>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblBill WHERE UserID=?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, userID);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Bill b = new Bill();
                b.setBillID(rs.getInt("BillID"));
                b.setDate(rs.getDate("Date"));
                b.setMoneyAmount(rs.getDouble("MoneyAmount"));
                b.setMonth(rs.getInt("Month"));
                b.setYear(rs.getInt("Year"));
                b.setNotes(rs.getNString("Notes"));
                User u = UserDAO.getInstance().getUserByID(rs.getInt("UserID"));
//                b.setUser(u);
//                Student stu = StudentDAO.getInstance().getStudentByID(rs.getInt("StudentID"));
//                b.setStudent(stu);
                Course c = CourseDAO.getInstance().getCourseByID(rs.getInt(rs.getInt("CourseID")));
                b.setCourse(c);
                lstBill.add(b);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BillDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstBill;
    }  

    @Override
    public List<Bill> getBillsByUserID(int userID) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Bill> getBillsByStudentID(int studentID) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Bill> getBillsByCourseID(int courseID) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
}
